package cvs;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class OrgSetMain {

	public static void main(String[] args) {
		OrgSetMain orgSet = new OrgSetMain();

		// do it before
		// update tb_CustomerOrg set account_type=null, is_indir= null

		System.out.println("Start orgSet");

		orgSet.doit();

		System.out.println("end orgSet");
	}

	public ArrayList<HashMap<String, String>> buildList() {
		ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		String sql = "select pos_id_sap, sum(distinct account_type_num) type from tb_CustomerOrg where valid=1 and pos_id_sap is not null "
				+ "group by pos_id_sap  ";

		PreparedStatement ps;
		ResultSet rs;

		DBConnectorGSBN connector = new DBConnectorGSBN();

		try {
			connector.start();
			Connection connection = connector.getConnection();

			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				HashMap<String, String> row = new HashMap<String, String>();

				row.put("pos_id_sap", rs.getString("pos_id_sap"));
				row.put("type", rs.getString("type"));

				list.add(row);

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connector.close();
		}

		return list;
	}

	int cnt = 0;

	public void doit() {
		ArrayList<HashMap<String, String>> list = buildList();

		DBConnectorGSBN gsbnconnection = new DBConnectorGSBN();

		try {
			gsbnconnection.start();
			Connection connection = gsbnconnection.getConnection();

			for (HashMap<String, String> row : list) {
				String pos_id_sap = row.get("pos_id_sap");
				String type = row.get("type");
				check(pos_id_sap, type, connection);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			gsbnconnection.close();
		}
	}

	private void check(String pos_id_sap, String type, Connection connection)
			throws SQLException {
		String account_type = null;
		int is_indir = 0;

		if (is_dir(type)) {

			if (exist_sap(pos_id_sap, connection) > 0) {
				account_type = "DIR";

				if (is_indir(type))
					is_indir = 1;
			} else if (is_indir(type))
				account_type = "INDIR";
			else if (is_end(type))
				account_type = "END";
			else
				account_type = "DIR";
		} else if (is_indir(type)) {
			account_type = "INDIR";
			is_indir = 1;
		} else if (is_end(type)) {
			account_type = "END";
		}

		//
		String sql = "update tb_CustomerOrg set account_type=?, is_indir=? where OrgID in " +
				"( select MAX(orgid) from tb_CustomerOrg where pos_id_sap=? and orgset=? )";
		
		PreparedStatement ps;
		ps = connection.prepareStatement(sql);

		int i = 0;
		ps.setString(++i, account_type);
		ps.setInt(++i, is_indir);
		ps.setString(++i, pos_id_sap);
		ps.setString(++i, account_type);

		++cnt;
		// System.out.println(cnt + " account_type=" + account_type
		// + " , is_indir=" + is_indir + " , pos_id_sap=" + pos_id_sap);
		ps.execute();
	}

	private int exist_sap(String pos_id_sap, Connection connection)
			throws SQLException {

		String sql = "select count(*) cnt from tb_CustomerSAP where OrgID in ( select orgid from tb_CustomerOrg where pos_id_sap=? )";
		PreparedStatement ps;
		ResultSet rs;

		ps = connection.prepareStatement(sql);

		ps.setString(1, pos_id_sap);

		rs = ps.executeQuery();

		if (rs.next())
			return rs.getInt("cnt");

		return 0;
	}

	private boolean is_dir(String orgNum) {
		if (orgNum.equals("1"))
			return true;

		if (orgNum.equals("3"))
			return true;

		if (orgNum.equals("5"))
			return true;

		if (orgNum.equals("7"))
			return true;

		return false;
	}

	private boolean is_indir(String type) {
		if (type.equals("2"))
			return true;

		if (type.equals("3"))
			return true;

		if (type.equals("6"))
			return true;

		if (type.equals("7"))
			return true;

		return false;
	}

	private boolean is_end(String type) {
		if (type.equals("4"))
			return true;

		if (type.equals("5"))
			return true;

		if (type.equals("6"))
			return true;

		if (type.equals("7"))
			return true;

		return false;
	}
}
